package com.zx.oa.dao.work;

import com.zx.oa.dao.BaseDao;
import com.zx.oa.entity.CheckWork;
import com.zx.oa.entity.Work;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class WorkDaoImpl extends BaseDao implements WorkDao {

    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;

    @Override
    public List<Work> getWorkList(String queryMajor, String queryClassName, int currentPageNo, int pageSize) {

        List<Work> workList = new ArrayList<>();
        //可变长度字符串
        StringBuffer sb = new StringBuffer("SELECT zs.`studentId`, zm.`major`, zc.`className`, zs.`studentName`\n" +
                "FROM ztestudent zs\n" +
                "LEFT JOIN zteclassinfo zc ON zc.`classId` = zs.`classId`\n" +
                "LEFT JOIN ztemajor zm ON zm.`id` = zc.`studyType`\n" +
                "LEFT JOIN ztecheckwork zw ON zw.`studentNo` = zs.`studentId`\n" +
                "WHERE 1=1\n" +
                "AND zw.`ckstatu` IS NULL");
        conn = this.getConnection();
        //按照顺序给？拼接参数的值
        List<Object>params=new ArrayList<>();
        if(null!=queryMajor&&!"".equals(queryMajor)){
            sb.append(" AND zm.`major` LIKE ?");
            params.add('%'+queryMajor+'%');
        }
        if(null!=queryClassName&&!"".equals(queryClassName)){
            sb.append(" AND zc.`className` LIKE ?");
            params.add('%'+queryClassName+'%');
        }
        sb.append(" LIMIT ?,?");
        params.add((currentPageNo-1)*pageSize);
        params.add(pageSize);

        try {
            pstmt = conn.prepareStatement(sb.toString());
            //依次给?参数赋值
            for(int i=0;i<params.size();i++){
                pstmt.setObject((i+1),params.get(i));
            }
            rs = pstmt.executeQuery();
            while (rs.next()) {
                int studentId = rs.getInt("studentId");
                String major = rs.getString("major");
                String className = rs.getString("className");
                String studentName = rs.getString("studentName");

                Work work = new Work(studentId,major,className,studentName);
                workList.add(work);
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            this.closeAll(conn,pstmt,rs);
        }
        return workList;
    }

    @Override
    public int getWorkTotalCount(String queryMajor, String queryClassName) {

        int count=-1;//总记录数
        //可变长度字符串
        StringBuffer sb = new StringBuffer("SELECT COUNT(*)\n" +
                "FROM ztestudent zs\n" +
                "LEFT JOIN zteclassinfo zc ON zc.`classId` = zs.`classId`\n" +
                "LEFT JOIN ztemajor zm ON zm.`id` = zc.`studyType`\n" +
                "LEFT JOIN ztecheckwork zw ON zw.`studentNo` = zs.`studentId`\n" +
                "WHERE 1=1\n" +
                "AND zw.`ckstatu` IS NULL");
        conn = this.getConnection();
        //按照顺序给？拼接参数的值
        List<Object>params=new ArrayList<>();
        if(null!=queryMajor&&!"".equals(queryMajor)){
            sb.append(" AND zm.`major` LIKE ?");
            params.add('%'+queryMajor+'%');
        }
        if(null!=queryClassName&&!"".equals(queryClassName)){
            sb.append(" AND zc.`className` LIKE ?");
            params.add('%'+queryClassName+'%');
        }
        try {
            pstmt = conn.prepareStatement(sb.toString());
            //依次给?参数赋值
            for(int i=0;i<params.size();i++){
                pstmt.setObject((i+1),params.get(i));
            }
            rs = pstmt.executeQuery();
            while (rs.next()) {
                count=rs.getInt(1);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            this.closeAll(conn,pstmt,rs);
        }
        return count;
    }

    @Override
    public List<Work> getWorkMajorList() {

        List<Work> workList = new ArrayList<>();
        //可变长度字符串
        StringBuffer sb = new StringBuffer("SELECT zm.`major`\n" +
                "FROM ztestudent zs\n" +
                "LEFT JOIN zteclassinfo zc ON zc.`classId` = zs.`classId`\n" +
                "LEFT JOIN ztemajor zm ON zm.`id` = zc.`studyType`\n" +
                "GROUP BY zm.`major`");
        conn = this.getConnection();
        try {
            pstmt = conn.prepareStatement(sb.toString());
            rs = pstmt.executeQuery();
            while (rs.next()) {
                String major = rs.getString("major");
                String className = null;

                Work work = new Work(major,className);
                workList.add(work);
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            this.closeAll(conn,pstmt,rs);
        }
        return workList;
    }

    @Override
    public List<Work> getWorkClassNameList() {

        List<Work> workList = new ArrayList<>();
        //可变长度字符串
        StringBuffer sb = new StringBuffer("SELECT zc.`className`\n" +
                "FROM ztestudent zs\n" +
                "LEFT JOIN zteclassinfo zc ON zc.`classId` = zs.`classId`\n" +
                "LEFT JOIN ztemajor zm ON zm.`id` = zc.`studyType`\n" +
                "GROUP BY zc.`className`");
        conn = this.getConnection();
        try {
            pstmt = conn.prepareStatement(sb.toString());
            rs = pstmt.executeQuery();
            while (rs.next()) {
                String major = null;
                String className = rs.getString("className");

                Work work = new Work(major,className);
                workList.add(work);
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            this.closeAll(conn,pstmt,rs);
        }
        return workList;
    }

    @Override
    public boolean upsetWork(CheckWork checkWork) {
        String sql="INSERT INTO ztecheckwork(studentNo,ckTime,ckstatu)\n" +
                "VALUES(?,?,?);";
        Object [] param={
                checkWork.getStudentNo(),
                checkWork.getCkTime(),
                checkWork.getCkStatu()
        };
        if(this.executeUpdateInfo(sql,param)>0){
            return  true;
        }
        return false;
    }

//    @Override
//    public List<Work> getWorkClassNameList(String selectMajor) {
//        List<Work> workList = new ArrayList<>();
//        //可变长度字符串
//        StringBuffer sb = null;
//        if (null!=selectMajor&&!"".equals(selectMajor)) {
//            sb = new StringBuffer("SELECT zs.`studentId`, zm.`major`, zc.`className`, zs.`studentName`\n" +
//                    "FROM ztestudent zs\n" +
//                    "LEFT JOIN zteclassinfo zc ON zc.`classId` = zs.`classId`\n" +
//                    "LEFT JOIN ztemajor zm ON zm.`id` = zc.`studyType`\n" +
//                    "WHERE zm.`major` LIKE ?\n" +
//                    "GROUP BY zc.`className`;");
//        }
//        sb = new StringBuffer("SELECT zs.`studentId`, zm.`major`, zc.`className`, zs.`studentName`\n" +
//                "FROM ztestudent zs\n" +
//                "LEFT JOIN zteclassinfo zc ON zc.`classId` = zs.`classId`\n" +
//                "LEFT JOIN ztemajor zm ON zm.`id` = zc.`studyType`\n" +
//                "GROUP BY zc.`className`");
//        conn = this.getConnection();
//        try {
//            pstmt = conn.prepareStatement(sb.toString());
//            if (null!=selectMajor&&!"".equals(selectMajor)) {
//                pstmt.setString(1,selectMajor);
//            }
//            rs = pstmt.executeQuery();
//            while (rs.next()) {
//                int studentId = rs.getInt("studentId");
//                String major = rs.getString("major");
//                String className = rs.getString("className");
//                String studentName = rs.getString("studentName");
//
//                Work work = new Work(studentId,major,className,studentName);
//                workList.add(work);
//            }
//
//        } catch (SQLException e) {
//            throw new RuntimeException(e);
//        } finally {
//            this.closeAll(conn,pstmt,rs);
//        }
//        return workList;
//    }
}
